package com.example.wnn.utils;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.example.wnn.config.exception.BusinessException;
import com.example.wnn.develop.ieport.importz.ExcelHead;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public class ImportExcelUtils {

    /**
     * 定义全局的常量值
     */
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";


    /**
     * Excel 转换数据
     * @param file
     * @param sheetName
     * @param c
     * @return
     */
    public static String excelCoverData(MultipartFile file, String sheetName, Class c) {
        try {
            if (Objects.isNull(file) || StrUtil.isBlank(file.getOriginalFilename()) ){
                throw new Exception("文件为空"); //todo 使用者可以改为自己定义的异常
            }
            List<Map<String,Object>> result = new ArrayList<>();
            Workbook workbook  = createWorkbook(file.getInputStream(),file.getOriginalFilename());
            // 获取sheet
            Sheet sheet = createSheet(sheetName,workbook);
            Row tableHeard =  sheet.getRow(0);
            if (Objects.isNull(tableHeard)){
                throw new Exception("未获取到表头");
            }
            List<String> head = new ArrayList<>();
            for (Cell cell : tableHeard) {
                head.add(getCellValue(cell).trim());
            }
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum == 0) {
                throw new Exception("文件中没有数据");
            }
            // 有数据时才处理 得到类的所有field.
            Field[] allFields = c.getDeclaredFields();
            // 定义一个map用于存放列的序号和field.
            Map<String, String> fieldsMap = new HashMap<>();
            for (int col = 0; col < allFields.length; col++)
            {
                Field field = allFields[col];
                ExcelHead attr = field.getAnnotation(ExcelHead.class);
                if (Objects.nonNull(attr)) {
                    fieldsMap.put(attr.name(), field.getName());
                }
            }
            for (int i = 1; i < lastRowNum + 1; i++) {
                Row row = sheet.getRow(i);
                Map<String,Object> rowMap = new HashMap<>();
                if (row != null) {
                    // 读取cell单元格内容
                    for (int j=0; j<head.size();j++){
                        String headName = head.get(j);
                        if (fieldsMap.containsKey(headName)){
                            String var = getCellValue(row.getCell(j));
                            rowMap.put(fieldsMap.get(headName),var);
                        }

                    }
                }
                result.add(rowMap);
            }
           return JSONUtil.toJsonStr(result);

        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return null;
    }

    private static Workbook createWorkbook(InputStream is, String filename)throws Exception{
        Workbook workbook = null;
        if (filename.endsWith(XLS) || filename.endsWith(XLSX)) {
            workbook = WorkbookFactory.create(is);
        } else {
            throw new Exception("文件不是Excel文件");
        }
        if (null == workbook){
            throw new Exception("创建workbook失败！");
        }
        return workbook;
    }

    private static Sheet createSheet(String sheetName,Workbook wb)throws Exception{
        Sheet sheet = null;
        if (StrUtil.isNotBlank(sheetName))
        {
            // 如果指定sheet名,则取指定sheet中的内容.
            sheet = wb.getSheet(sheetName);
        }
        else
        {
            // 如果传入的sheet名不存在则默认指向第1个sheet.
            sheet = wb.getSheetAt(0);
        }
        if (sheet == null)
        {
            throw new Exception("文件sheet不存在");
        }
        return sheet;
    }


    /**
     * 获取每个单元格内容
     *
     * @param cell cell
     * @return String
     */
    private  static String getCellValue(Cell cell) {
        // 单元格内容
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                // 数字
                case HSSFCell
                        .CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue() + "";
                    // 判断cell是不是日期类型的
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 获取日期类型的单元格
                        Date date = cell.getDateCellValue();
                        if (null != date) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                // 字符串
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                // Boolean
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue() + "";
                    break;
                // 公式
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula() + "";
                    break;
                // 空格
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                // 错误
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }
}
